﻿using ImportDataFileter.Common;
using ImportDataFileter.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace ImportDataFileter.Controllers
{
    public class SearchController : BaseController
    {
        public ImportDataFileterEntitiy db = null;

        [HttpGet]
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(int? DataDource, int? DataType)
        {
            string FilterFilePath = string.Empty;
            if (DataDource == null || DataDource < 0)
                return RedirectToAction("Index", "Search", new { message = 200 });
            if (DataType == null || DataType < 0)
                return RedirectToAction("Index", "Search", new { message = 300 });
            //连接数据库
            db = new ImportDataFileterEntitiy((int)DataDource);
            try
            {
                var files = Request.Files;
                if (files != null && files.Count > 0)
                {
                    #region  执行多个文件上传
                    for (int i = 0; i < files.Count; i++)
                    {
                        HttpPostedFileBase fileitem = files[i];
                        //判定文件的大小  
                        string strExtension = Path.GetExtension(fileitem.FileName);
                        if (strExtension == ".xls" || strExtension == ".xlsx")
                        {
                            //创建文件
                            string filePath = "/Upload/";
                            Directory.CreateDirectory(Server.MapPath(filePath));
                            //创建唯一的文件名
                            string fFullDir = filePath + fileitem.FileName;
                            fileitem.SaveAs(Server.MapPath(fFullDir));
                            //得到表格里面的数据
                            #region 数据筛选
                            DataTable ImportFileData = NPOIExcelHelper.ExcelToDataTable(Server.MapPath(fFullDir), true, 0);
                            if (ImportFileData != null && ImportFileData.Rows.Count > 0)
                            {

                                //数据筛选文件保存文夹
                                Directory.CreateDirectory(Server.MapPath(filePath + "/Filter/"));
                                //数据库交互

                                switch (DataType)
                                {
                                    case 0://机构数据
                                        FilterFilePath = OrganizationFilter(ImportFileData, Server.MapPath(filePath + "/Filter/" + fileitem.FileName));
                                        break;
                                    case 1://人员数据
                                        FilterFilePath = UsersFilter(ImportFileData, Server.MapPath(filePath + "/Filter/" + fileitem.FileName));
                                        break;
                                    case 2://学分数据
                                        FilterFilePath = CreditFilter(ImportFileData, Server.MapPath(filePath + "/Filter/" + fileitem.FileName));
                                        break;
                                    default:
                                        break;
                                }
                                if (!string.IsNullOrEmpty(FilterFilePath))
                                {
                                    ViewBag.FilterFilePath = filePath + "/Filter/" + fileitem.FileName;
                                }
                            }
                            else
                            {
                                return RedirectToAction("Index", "Search", new { message = 100 });
                            }
                            #endregion
                        }
                        else
                        {
                            return RedirectToAction("Index", "Search", new { message = 500 });
                        }

                    }
                    #endregion
                }
                return View();
            }
            catch (Exception)
            {
                return RedirectToAction("Index", "Search", new { message = 000 });
            }
        }

        #region 学分数据筛选方法
        private string CreditFilter(DataTable importFileData, string SavePath)
        {
            List<int> ErrorCol = new List<int>();
            foreach (DataRow item in importFileData.Rows)
            {
                int Col = importFileData.Rows.IndexOf(item);
                //单独取值
                string IDCard = (string)item["身份证号"];
                string UserName = (string)item["学员姓名"];
                string CreaditCategoryName = (string)item["学分子类别"];
                DateTime CreditDate = (DateTime)item["时间"].IsNull(DateTime.Now);
                string ProjectName = (string)item["项目名称"];
                //  //判断格式是否问题
                if (IDCard.Trim().Length != 18 || IDCard.Trim().Length != 15) continue;
                //根据姓名、身份证判断用户是否存在
                var UserModel = db.tb_User.Where(t => t.User_Name == UserName && t.IDCard == IDCard).FirstOrDefault();
                if (UserModel == null)
                {
                    ErrorCol.Add(Col);
                    continue;
                }
                //判断 学分类别 是否存在
                var CreaditCategoryModel = db.
                    tb_CreaditCategory.Where(t => t.CreaditCategory_Name == CreaditCategoryName).
                    FirstOrDefault();
                if (CreaditCategoryModel == null)
                {
                    ErrorCol.Add(Col);
                    continue;
                }
                //录入方式、学分这几个字段一起来进行重复性判定(暂时不判断)

                //判断时间、项目名称，重复性判断
                var ItemCreditModel = db.tb_Credit
                                .Where(t => t.CreditDate == CreditDate && t.CreditDescribe == ProjectName && t.User_ID == UserModel.id)
                                .FirstOrDefault();
                if (ItemCreditModel != null)
                {
                    ErrorCol.Add(Col);
                    continue;
                }
            }
            MemoryStream ms = NPOIExcelHelper.ExportFromDataTable(importFileData, ".xls", ErrorCol);
            using (FileStream fs = new FileStream(SavePath, FileMode.Create, FileAccess.Write))//保存到某某文件夹
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
            //页面上显示筛选的文件路径
            return SavePath;
        }
        #endregion

        #region 人员数据筛选方法
        private string UsersFilter(DataTable importFileData, string SavePath)
        {
            List<int> ErrorCol = new List<int>();
            foreach (DataRow item in importFileData.Rows)
            {

                int Col = importFileData.Rows.IndexOf(item);
                tb_User ItemModel = CreateUserModel(item);
                #region 基础数据判断
                //判断格式是否问题
                if (ItemModel.IDCard.Trim().Length != 18 || ItemModel.IDCard.Trim().Length != 15) continue;

                #endregion
                //查询 姓名+身份证  判断是否是存在该用户
                var UserModel = db.tb_User.Where(t => t.User_Name == ItemModel.User_Name && t.IDCard == ItemModel.IDCard).FirstOrDefault();
                if (UserModel != null)
                {
                    ErrorCol.Add(Col);
                    continue;
                }
                //判断当前的机构和科室是否存在
                //机构是否存在
                var ItemDepartmentModel = db.Base_Organization.Where(t => t.Organization_Name == ItemModel.User_Department).FirstOrDefault();
                if (ItemDepartmentModel == null)
                {
                    ErrorCol.Add(Col);
                    continue;
                }
                //科室是否存在
                var ItemUnitModel = db.Base_Organization.Where(
                    t => t.Organization_Name == ItemModel.User_Unit &&
                         t.ParentId == ItemDepartmentModel.Organization_ID).FirstOrDefault();
                if (ItemUnitModel == null)
                {
                    ErrorCol.Add(Col);
                    continue;
                }
                //判断职称是否存在
                var ItemTitleModel = db.tb_Title.Where(t => t.Title_Name == ItemModel.Title_ID).FirstOrDefault();
                if (ItemTitleModel == null)
                {
                    ErrorCol.Add(Col);
                    continue;
                }
                //模板数据为空,暂时不判断
                //判断专业是否存在
                //判断学科是否存在
            }
            MemoryStream ms = NPOIExcelHelper.ExportFromDataTable(importFileData, ".xls", ErrorCol);
            using (FileStream fs = new FileStream(SavePath, FileMode.Create, FileAccess.Write))//保存到某某文件夹
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
            //页面上显示筛选的文件路径
            return SavePath;
        }
        #endregion

        #region 机构数据筛选方法
        private string OrganizationFilter(DataTable importFileData, string SavePath)
        {
            //初始化错误数据单元格或者行的二维或者一维数组
            List<int> ErrorCol = new List<int>();
            //int[,] ErrorCol = new int[importFileData.Rows.Count, 2];
            List<Base_Organization> CurrentOrgList = new List<Base_Organization>();
            foreach (DataRow item in importFileData.Rows)
            {
                string ItemLevelName = (string)item["Level"];
                string OrgListItemName = (string)item["name"];
                int ThisLevelCount = OrgListItemName.LastIndexOf("=");

                var LevelModel = db.tb_Level.Where(t => t.Level_Name == ItemLevelName).FirstOrDefault();
                if (LevelModel != null && ThisLevelCount > 0)
                {
                    OrgListItemName = OrgListItemName.Substring(ThisLevelCount + 1);
                    var OrgModelList =
                        db.Base_Organization.Where(
                        t =>
                        t.Organization_Name == OrgListItemName &&
                        t.Organization_Level == LevelModel.id
                        ).ToList();

                    if (OrgModelList == null)
                    {
                        //不存在，只需要本地比较
                        int index = importFileData.Rows.IndexOf(item); //index 为索引值
                        if (index != importFileData.Rows.Count - 1 && LevelModel.Level_Name != "科室")
                        {
                            string NextRowName = (string)importFileData.Rows[index + 1]["name"];
                            int NextRowQueIndex = NextRowName.LastIndexOf("=");
                            if (NextRowQueIndex < ThisLevelCount)//当前这个行小于小于下有一行，标记颜色
                            {
                                ErrorCol.Add(index + 1);
                            }
                        }
                    }
                    else
                    {
                        //存在,上色,存储他名字,GUID,
                        //递归该机构下面的所有单位以及科室
                        // CurrentOrgList.AddRange(GetChildren(OrgModelList[0].Organization_ID).ToList());

                        //如果当前数据存在并且他的上级存在，就上色

                    }
                }
                else
                {
                    //给单元格或者行上色错误颜色
                    //错误颜色规范  
                    int Col = importFileData.Rows.IndexOf(item);
                    // To_Array[j, i] = From_Array[i][j];
                    ErrorCol.Add(Col);
                }

            }
            MemoryStream ms = NPOIExcelHelper.ExportFromDataTable(importFileData, ".xls", ErrorCol);
            using (FileStream fs = new FileStream(SavePath, FileMode.Create, FileAccess.Write))//保存到某某文件夹
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
            //页面上显示筛选的文件路径
            return SavePath;
        }
        #endregion

        #region 基础方法
        private IEnumerable<Base_Organization> GetChildren(string PId)
        {
            var query = db.Base_Organization.Where(p => p.ParentId == PId);
            return query.ToList().Concat(query.ToList().SelectMany(t => GetChildren(t.Organization_ID)));
        }

        private tb_User CreateUserModel(DataRow item)
        {

            tb_User model = new tb_User();
            model.User_Name = (string)item["学员姓名"];
            model.IDCard = (string)item["身份证号"];
            model.User_Unit = (string)item["单位"];
            model.User_Department = (string)item["科室"];
            model.Title_ID = (string)item["职称"];
            return model;
        }
        #endregion

        #region 资源销毁
        protected override void Dispose(bool disposing)
        {
            DeleteDbContext();
            base.Dispose(disposing);
        }

        private void DeleteDbContext()
        {
            if (db != null)
            {
                db.Dispose();
                db = null;
            }
        }
        #endregion
    }
}